﻿CREATE PROCEDURE [dbo].[PTOGetCategoriesByUserx] (@userID int, @startDate datetime, @endDate datetime)
AS
BEGIN

	SET NOCOUNT ON;
	declare @tbl as table(
	CategoryID int,
	Category nvarchar(100),
	Hours decimal(10,2)
	)
	declare @RolledPTOHours int
	select @RolledPTOHours = Coalesce(PTOHoursRolledIntoCurrentYear,0) from dbo.Users
	where UserID=@userID

	insert into @tbl 
	select  el.CategoryID, c.[Name] as Category, sum(duration)  as Hours from EntryLog el
	inner join Categories c on el.CategoryID = c.CategoryID
	where UserID = @userID and EntryDate between @startDate and @endDate and el.CategoryID in (4,7,13) 
	group by UserID, el.CategoryID, c.[Name] 
	
	update @tbl set Hours=Hours-@RolledPTOHours where CategoryID=13
	select * from @tbl

	declare @expectedHoursRatio float
	declare @partialYearRatio float

	declare @eeStartDate datetime
	declare @startYear int
	declare @startMonth int
	declare @currentYear int
	declare @currentMonth int
	declare @holidayCountForEmployee int

	set @eeStartDate = (select StartDate from Users where UserID = @userID)
	set @startYear = convert(int,(select year(@eeStartDate)))
	set @startMonth = convert(int,(select month(@eeStartDate)))
	
	set @currentYear = convert(int, (select year(getdate())))
	set @currentMonth = convert(int, (select month(getdate())))

	if (@startYear < @currentYear)
		begin
			set @partialYearRatio = 1
			set @holidayCountForEmployee = 8
		end
	else
		begin
			set @partialYearRatio =
				case 
					when @startMonth = 1 then 1
					when @startMonth = 2 then .9167
					when @startMonth = 3 then .8333
					when @startMonth = 4 then .75
					when @startMonth = 5 then .6667
					when @startMonth = 6 then .5833
					when @startMonth = 7 then .5
					when @startMonth = 8 then .4167
					when @startMonth = 9 then .3333
					when @startMonth = 10 then .25
					when @startMonth = 11 then .1667
					when @startMonth = 12 then .0833
				end;

			set @holidayCountForEmployee =
				case 
					when @startMonth = 1 then 7
					when @startMonth = 2 then 7
					when @startMonth = 3 then 7
					when @startMonth = 4 then 7
					when @startMonth = 5 then 7
					when @startMonth = 6 then 6
					when @startMonth = 7 then 6
					when @startMonth = 8 then 5
					when @startMonth = 9 then 5
					when @startMonth = 10 then 4
					when @startMonth = 11 then 4
					when @startMonth = 12 then 2
				end;
		end

	set @expectedHoursRatio = ((select ExpectedHours from Users where UserID = @userID) / convert(float, 40) )

	select UserTypeID,
		@startYear as StartYear, 
		@startMonth as StartMonth, 
		@expectedHoursRatio as ExpectedHoursRatio, 
		@partialYearRatio as PartialYearRatio, 
		round((120 * @expectedHoursRatio * @partialYearRatio), 0) as PTOAllotment, 
		round(((8 * @holidayCountForEmployee) * @expectedHoursRatio), 0) as HolidayAllotment
	from Users
	where UserID = @userID

END